﻿Imports System.Data.OleDb
Public Class GiaoVienDAO
    Public Shared Function SelectAll() As List(Of GiaoVienDTO)
        Const strSql As String = "select MAGIAOVIEN, TENGIAOVIEN, GIOITINH, NGAYSINH, DIACHI, SODT, EMAIL, MAMON from GIAOVIEN"
        Dim ds = New List(Of GiaoVienDTO)
        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                While re.Read()
                    Dim obj = New GiaoVienDTO
                    obj.MAGIAOVIEN = re.GetInt32(0)
                    obj.TENGIAOVIEN = re.GetString(1)
                    obj.GIOITINH = re.GetString(2)
                    obj.NGAYSINH = re.GetString(3)
                    obj.DIACHI = re.GetString(4)
                    obj.SODT = re.GetString(5)
                    obj.EMAIL = re.GetString(6)
                    obj.MAMON = re.GetInt32(7)
                    'obj.TENMON = re.GetString(8)
                    ds.Add(obj)
                End While
            End Using
        End Using

        Return ds
    End Function
    Public Shared Function LayDSGiaoVien() As List(Of GiaoVienDTO)
        Dim str = "select * from GIAOVIEN"
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of GiaoVienDTO)

        While reader.Read()
            Dim gv As New GiaoVienDTO
            gv.MAGIAOVIEN = reader.GetInt32(0)
            gv.TENGIAOVIEN = reader.GetString(1)
            gv.GIOITINH = reader.GetString(2)
            gv.NGAYSINH = reader.GetString(3)
            gv.DIACHI = reader.GetString(4)
            gv.SODT = reader.GetString(5)
            gv.EMAIL = reader.GetString(6)
            gv.MAMON = reader.GetInt32(7)

            ds.Add(gv)
        End While
        conn.Close()
        Return ds
    End Function

    Public Shared Function Them(ByVal gv As GiaoVienDTO) As Integer
        Using db = New SQLProvider

            Dim strSql = String.Format("Insert into GIAOVIEN ( MAGIAOVIEN,TENGIAOVIEN, GIOITINH,NGAYSINH,DIACHI,SODT,EMAIL,MAMON) values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}','{6}','{7}')", _
                                             gv.MAGIAOVIEN, gv.TENGIAOVIEN, gv.GIOITINH, gv.NGAYSINH, gv.DIACHI, gv.SODT, gv.EMAIL, gv.MAMON)

            Return db.ExecNoneQuery(strSql)
        End Using
    End Function

    Public Shared Function SuaGiaoVien(ByVal gv As GiaoVienDTO) As Integer
        Dim str = String.Format("update GIAOVIEN set TENGIAOVIEN='{0}', GIOITINH='{1}', NGAYSINH='{2}', DIACHI='{3}', SODT='{4}', EMAIL='{5}',MAMON ={6} where MAGIAOVIEN={7}", gv.TENGIAOVIEN, gv.GIOITINH, gv.NGAYSINH, gv.DIACHI, gv.SODT, gv.EMAIL, gv.MAMON, gv.MAGIAOVIEN)
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(Str, conn)
        Dim result = -1
        result = cmd.ExecuteNonQuery()
        conn.Close()
        Return result
    End Function

    Public Shared Function XoaGiaoVien(ByVal gv As GiaoVienDTO) As Integer
        Dim str = String.Format("delete from GIAOVIEN where MAGIAOVIEN={0}", gv.MAGIAOVIEN)
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim result = -1
        result = cmd.ExecuteNonQuery()
        conn.Close()
        Return result
    End Function

    Public Shared Function SelectByMaMon(ByVal MaMon As Integer) As List(Of GiaoVienDTO)
        Dim strSql As String = String.Format("select MAGIAOVIEN,TENGIAOVIEN, GIOITINH, NGAYSINH, DIACHI, SODT, EMAIL, MAMON from GIAOVIEN where MaMon = {0}", MaMon)
        Dim ds = New List(Of GiaoVienDTO)
        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                While re.Read()
                    Dim obj = New GiaoVienDTO
                    'obj.MALOP = re.GetString(0)
                    obj.MAGIAOVIEN = re.GetInt32(0)
                    obj.TENGIAOVIEN = re.GetString(1)
                    obj.GIOITINH = re.GetString(2)
                    obj.NGAYSINH = re.GetString(3)
                    obj.DIACHI = re.GetString(4)
                    obj.SODT = re.GetString(5)
                    obj.EMAIL = re.GetString(6)
                    obj.MAMON = re.GetInt32(7)
                    ds.Add(obj)
                End While
            End Using
        End Using

        Return ds
    End Function
End Class
